﻿Public Class maspenname
    Private pennameID = Nothing
    Private PENNAME = Nothing
    Private PENLNAME = Nothing
    Private SEX = Nothing
    Private pennameStatus = Nothing
    'Private selectMasprename = "SELECT PRENAME, FTPRENAME, FEPRENAME, STPRENAME, SEPRENAME, SEX, convert(bit, [STATUS]) FROM MASPRENAME"
    Private selectMaspenname = "SELECT maspenname.`penid` AS 'รหัสนามแฝง', maspenname.`penname` AS 'ชื่อนามแฝง', maspenname.`penlname` AS 'นามสกุลแฝง', massex.`sexdesc` AS 'เพศ', maspenname.`status` AS 'สถานะการเปิดใช้งาน', maspenname.`SEX` FROM maspenname LEFT JOIN (SELECT * FROM  massex WHERE `status` = 1) AS massex ON maspenname.sex = massex.sex"
    Dim connect As CONDB = CONDB.NewConnection()
    'Dim connectlocal As conlocal = conlocal.NewConnection

    Property pennameID_ As Integer
        Get
            Return pennameID
        End Get
        Set(value As Integer)
            pennameID = value
        End Set
    End Property

    Property PENNAME_ As String
        Get
            Return PENNAME
        End Get
        Set(value As String)
            PENNAME = value
        End Set
    End Property
    Property PENLNAME_ As String
        Get
            Return PENLNAME
        End Get
        Set(value As String)
            PENLNAME = value
        End Set
    End Property

    Property SEX_ As Integer
        Get
            Return SEX
        End Get
        Set(value As Integer)
            SEX = value
        End Set
    End Property

    Property pennameStatus_ As Boolean
        Get
            Return pennameStatus
        End Get
        Set(value As Boolean)
            If value = True Then
                pennameStatus = 1
            Else
                pennameStatus = 0
            End If
        End Set
    End Property

    Property selectMaspenname_ As String
        Get
            Return selectMaspenname
        End Get
        Set(value As String)

        End Set
    End Property
    Public Sub loadcombobox(ByVal MassexCmb As ComboBox)
        Dim MASSEX As DataTable
        MASSEX = connect.GetTable("SELECT `SEX`, `SEXDESC` FROM MASSEX WHERE `STATUS` = 1 ")
        Try
            With MassexCmb
                .DataSource = MASSEX
                .DisplayMember = "SEXDESC"
                .ValueMember = "SEX"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    'Public Function SEXCombobox() As DataTable
    '    connectlocal = conlocal.NewConnection()
    '    Return connectlocal.GetTable("SELECT SEX, SEXDESC FROM MASSEX WHERE [STATUS] = 1")
    'End Function

    Public Sub insertMaspennameData()
        If PENNAME <> "" And SEX.ToString.Length > 0 Then
            If TypeOf PENNAME Is String Then
                If connect.GetTable("SELECT PENNAME FROM MASPENNAME WHERE PENNAME = '" & PENNAME & "'").Rows.Count > 0 Then
                    MsgBox("มีข้อมูลนี้อยู่ในระบบแล้ว")
                Else
                    Try
                        connect = CONDB.NewConnection()
                        'connectlocal = conlocal.NewConnection()
                        Dim pennameID As Integer

                        pennameID = connect.ExecuteScalar("INSERT INTO maspenname (PENNAME,PENLNAME, SEX, STATUS) VALUES ('" & PENNAME & "','" & PENLNAME & "','" & SEX & "', '" & pennameStatus & "'); SELECT LAST_INSERT_ID();")
                        'connectlocal.ExecuteNonQuery("INSERT INTO MASPENNAME (PENNAME,PENLNAME, SEX, STATUS) VALUES ('" & PENNAME & "','" & PENLNAME & "','" & SEX & "', '" & pennameStatus & "')")
                        MsgBox("ท่านได้ทำการเพิ่มข้อมูลคำนำหน้าชื่อเรียบร้อยแล้ว")
                    Catch ex As Exception
                        MsgBox("มีข้อมูลนี้อยู่ในระบบอยู่แล้ว" & vbCrLf & ex.Message)
                    End Try

                    connect.Dispose()
                End If
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Sub

    Public Sub editMaspennameData()
        If pennameID > 0 And PENNAME <> "" And SEX.ToString.Length > 0 Then
            If TypeOf PENNAME Is String Then
                Try
                    connect = CONDB.NewConnection()
                    'connectlocal = conlocal.NewConnection()

                    connect.ExecuteNonQuery("UPDATE maspenname SET PENNAME = '" & PENNAME & "', PENLNAME = '" & PENLNAME & "' , SEX = '" & SEX & "', STATUS = '" & pennameStatus & "' WHERE PENID = '" & pennameID & "'")
                    'connectlocal.ExecuteNonQuery("UPDATE MASPENNAME SET PENNAME = '" & PENNAME & "',PENLNAME = '" & PENLNAME & "' , SEX = '" & SEX & "', STATUS = '" & pennameStatus & "' WHERE PRENAME = '" & pennameID & "'")
                    MsgBox("ท่านได้ทำการแก้ไขข้อมูลคำนำหน้าชื่อเรียบร้อยแล้ว")
                Catch ex As Exception
                    MsgBox("มีข้อมูลนี้อยู่ในระบบแล้ว" & vbCrLf & ex.Message)
                End Try

                connect.Dispose()
            Else
                MsgBox("โปรดกรุณาระบุข้อมูลให้ถูกต้อง ต้องเป็นตัวอักษร")
            End If
        Else
            MsgBox("ท่านต้องทำการกรอกข้อมูลให้ครบถ้วน")
        End If
    End Sub
End Class
